package com.mdsi.student;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mdsi.models.ConnectionManager;
import com.mdsi.models.Student;

/**
 * Servlet implementation class StudentServlet
 */
@WebServlet("/User/Student")
public class StudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public StudentServlet() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Student student = (Student) request.getSession().getAttribute("user") ;
		try {
			int totalAbsence = 0 ; 
			int absence_justify_obligatory  = 0;
			int absence_justify_non_obligatory = 0;
			int absence_non_justify_obligatory = 0;
			int absence_non_justify_non_obligatory = 0;
			Connection conn = ConnectionManager.getConnection() ;
			PreparedStatement st = conn.prepareStatement("SELECT COUNT (*) "
						+ "FROM ABSENCE A "
						+ "WHERE A.STUDENT.ID_STUDENT = " + student.getId()) ;
			ResultSet result = st.executeQuery() ;
			if (result.next()) {
				totalAbsence = result.getInt(1) ;
			}
			st = conn.prepareStatement("SELECT COUNT (*) "
						+ "FROM ABSENCE A "
						+ "WHERE A.STUDENT.ID_STUDENT = " + student.getId() + " "
							+ "AND A.LESSON.OBLIGATORY = 'Y' "
							+ "AND A.JUSTIFY = 'Y'") ;
			result = st.executeQuery() ;
			if (result.next()) {
				absence_justify_obligatory = result.getInt(1) ;
			}
			st = conn.prepareStatement("SELECT COUNT (*) "
					+ "FROM ABSENCE A "
					+ "WHERE A.STUDENT.ID_STUDENT = " + student.getId() + " "
						+ "AND A.LESSON.OBLIGATORY = 'N' "
						+ "AND A.JUSTIFY = 'Y'") ;
			result = st.executeQuery() ;
			if (result.next()) {
				absence_justify_non_obligatory = result.getInt(1) ;
			}
			st = conn.prepareStatement("SELECT COUNT (*) "
					+ "FROM ABSENCE A "
					+ "WHERE A.STUDENT.ID_STUDENT = " + student.getId() + " "
						+ "AND A.LESSON.OBLIGATORY = 'Y' "
						+ "AND A.JUSTIFY = 'N'") ;
			result = st.executeQuery() ;
			if (result.next()) {
				absence_non_justify_obligatory = result.getInt(1) ;
			}
			st = conn.prepareStatement("SELECT COUNT (*) "
					+ "FROM ABSENCE A "
					+ "WHERE A.STUDENT.ID_STUDENT = " + student.getId() + " "
						+ "AND A.LESSON.OBLIGATORY = 'N' "
						+ "AND A.JUSTIFY = 'N'") ;
			result = st.executeQuery() ;
			if (result.next()) {
				absence_non_justify_non_obligatory = result.getInt(1) ;
			}
			st = conn.prepareStatement("SELECT COUNT (*) "
					+ "FROM ABSENCE A "
					+ "WHERE A.STUDENT.ID_STUDENT = " + student.getId() + " "
						+ "AND A.LESSON.OBLIGATORY = 'Y' "
						+ "AND A.JUSTIFY = 'Y'") ;
			result = st.executeQuery() ;
			if (result.next()) {
				absence_justify_obligatory = result.getInt(1) ;
			}
			conn.close () ;
			getServletContext().setAttribute("TA", totalAbsence) ;
			getServletContext().setAttribute("AJO", absence_justify_obligatory) ;
			getServletContext().setAttribute("AJNO", absence_justify_non_obligatory) ;
			getServletContext().setAttribute("ANJO", absence_non_justify_obligatory) ;
			getServletContext().setAttribute("ANJNO", absence_non_justify_non_obligatory) ;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		this.getServletContext().getRequestDispatcher("/WEB-INF/student/student.jsp").forward(request, response);		
		
	}

}
